package org.madmac.maquina;

import org.madmac.exception.GenerateScriptErrorException;
import org.madmac.report.ReportHardwareMaquina;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;


import org.madmac.exception.InsertErrorException;
import org.wakeful.conn.Connection;

public class MaquinaDao {

   private static MaquinaDao instance;

   private MaquinaDao() {
   }

   public static MaquinaDao getInstance() {

      if (instance == null) {
         instance = new MaquinaDao();

      }
      return instance;
   }

   /**
    * seleciona todos os hosts
    * @deprecated
    */
   public List<Maquina> selectAll() throws SQLException, ClassNotFoundException, Exception {

      List<Maquina> lista = new ArrayList<Maquina>();

      java.sql.Connection conn = Connection.getInstance();
      Statement stmt = conn.createStatement();

      String sql = "SELECT id_maquina, id_setor, tombamento, id_pessoa, sistema_op FROM madmac.maquina ";

      ResultSet rs = null;
      try {
         rs = stmt.executeQuery(sql);


         while (rs.next()) {
            Maquina maquina = new Maquina();

            maquina.setIdMaquina(rs.getInt("id_maquina"));
            maquina.setIdPessoa(rs.getInt("id_pessoa"));
            maquina.setIdSetor(rs.getInt("id_setor"));
            maquina.setTombamento(rs.getInt("tombamento"));
            maquina.setSistemaOp(rs.getString("sistema_op"));

            lista.add(maquina);
         }
      } catch (Exception e) {
         return null;
      } finally {
         rs.close();
         if (rs != null) {
            rs.close();
         }
      }
      return lista;
   }

   /**
    * Insere um novo host.
    * @param maquina
    * @return
    * @throws SQLException
    * @throws ClassNotFoundException
    * @throws Exception
    * @deprecated
    */
   public boolean insertMaquina(Maquina maquina) throws SQLException, ClassNotFoundException, Exception {

      boolean resultado = false;
      java.sql.Connection conn = Connection.getInstance();
      PreparedStatement pstmt = null;
      try {
         pstmt = conn.prepareStatement(MaquinaQuery.create());
         pstmt.setInt(1, maquina.getSetor().getIdSetor());
         pstmt.setInt(2, maquina.getTombamento());
         pstmt.setInt(3, maquina.getPessoa().getIdPessoa());
         pstmt.setString(4, maquina.getSistemaOp());
         int count = pstmt.executeUpdate();
         resultado = false;

         if (count > 0) {
            resultado = true;
         }
      } catch (Exception e) {
         throw new Exception(e);
      } finally {
         if (pstmt != null) {
            pstmt.close();
         }
      }
      return resultado;
   }

   /**
    * Cria uma nova máquina e retorna o id_maquina.
    * @param maquina
    * @return
    * @throws Exception
    */
   public Object create(Maquina maquina) throws Exception {

      Integer id = null;
      PreparedStatement pstmt = null;
      ResultSet rs = null;
      try {
         java.sql.Connection conn = Connection.getInstance();
         pstmt = conn.prepareStatement(MaquinaQuery.create());
         pstmt.setObject(1, maquina.getSetor().getIdSetor(), Types.INTEGER);
         pstmt.setInt(2, maquina.getTombamento());
         pstmt.setObject(3, maquina.getPessoa().getIdPessoa(), Types.INTEGER);
         pstmt.setString(4, maquina.getSistemaOp());
         pstmt.setObject(5, maquina.getNomeDominio(), Types.VARCHAR);
         rs = pstmt.executeQuery();
         if (rs.next()) {
            id = rs.getInt("id_maquina");
         }
      } catch (Exception e) {
         throw new Exception(e);
      } finally {
         if (pstmt != null) {
            pstmt.close();
         }
         if (rs != null) {
            rs.close();
         }
      }
      return id;
   }

   /**
    * Deleta um host atraves de seu tombamento.
    * @param tombamento
    * @throws SQLException
    * @throws ClassNotFoundException
    * @throws Exception
    */
   public void deleteMaquinas(int tombamento) throws SQLException, ClassNotFoundException, Exception {

      java.sql.Connection conn = Connection.getInstance();
      PreparedStatement pstmt = null;
      try {
         String sql = "DELETE FROM madmac.maquina WHERE tombamento = ? ";
         pstmt = conn.prepareStatement(sql);
         pstmt.setInt(1, tombamento);
         pstmt.executeUpdate();
      } catch (Exception e) {
         throw new Exception(e);
      } finally {
         if (pstmt != null) {
            pstmt.close();
         }
      }
   }

   /**
    * Atualiza os dados referentes ao host.
    * @param maquina
    * @return
    * @throws SQLException
    * @throws ClassNotFoundException
    * @throws Exception
    * @deprecated
    */
   public boolean updateMaquinas(Maquina maquina) throws SQLException, ClassNotFoundException, Exception {

      boolean resultado = false;
      java.sql.Connection conn = Connection.getInstance();
      PreparedStatement pstmt = null;

      try {
         // DONE: usar MaquinaQuery.update()
         String sql = "UPDATE madmac.maquinas SET ";
         sql += "id_setor = ?, ";
         sql += "id_pessoa = ?, ";
         sql += "sistema_op = ? ";
         sql += "WHERE tombamento = ? ";
         pstmt = conn.prepareStatement(sql);
         pstmt.setInt(1, maquina.getSetor().getIdSetor());
         pstmt.setInt(2, maquina.getPessoa().getIdPessoa());
         pstmt.setString(3, maquina.getSistemaOp());
         pstmt.setInt(4, maquina.getTombamento());
         int count = pstmt.executeUpdate();

         if (count > 0) {
            resultado = true;
         }
      } catch (Exception e) {
         throw new Exception(e);
      } finally {
         if (pstmt != null) {
            pstmt.close();
         }
      }
      return resultado;
   }

   public boolean update(Maquina maquina) throws Exception {

      PreparedStatement pstmt = null;
      try {
         java.sql.Connection conn = Connection.getInstance();
         pstmt = conn.prepareStatement(MaquinaQuery.update());
         pstmt.setInt(1, maquina.getSetor().getIdSetor());
         pstmt.setInt(2, maquina.getPessoa().getIdPessoa());
         pstmt.setString(3, maquina.getSistemaOp());
         pstmt.setString(4, maquina.getNomeDominio());
         pstmt.setInt(5, maquina.getTombamento());
         pstmt.execute();
         return true;
      } catch (Exception e) {
         throw new Exception(e);
      } finally {
         if (pstmt != null) {
            pstmt.close();
         }
      }
   }

   /**
    * 
    * @param tombamento
    * @return
    * @throws Exception
    * @deprecated Use retrieveIdMaquina(int)
    */
   public Integer getIdMaquinaByTombamento(int tombamento) throws Exception {
      Integer idMaquina = null;
      java.sql.Connection conn = Connection.getInstance();
      String query = "SELECT id_maquina FROM madmac.maquina WHERE tombamento = ?";
      PreparedStatement pstmt = conn.prepareStatement(query);
      pstmt.setInt(1, tombamento);
      ResultSet rs = null;
      try {
         rs = pstmt.executeQuery();
         if (rs.next()) {
            idMaquina = rs.getInt("id_maquina");
         }
      } catch (Exception e) {
         throw new Exception(e);
      } finally {
         pstmt.close();
         if (rs != null) {
            rs.close();
         }
      }
      return idMaquina;
   }

   /**
    * seleciona dados acerca do host pelo tombamento
    * metodo utilizado em update_mac.jsp
    * DONE: criar metodo que retorna somente o idMaquina.
    * @deprecated Use retrieveIdMaquina(int)
    */
   public Maquina selectByTombamento(int tombamento) throws Exception {

      Maquina maquina = null;
      java.sql.Connection conn = Connection.getInstance();
      String sql = "SELECT * FROM madmac.maquina WHERE tombamento = ? ";
      PreparedStatement pstmt = conn.prepareStatement(sql);
      pstmt.setInt(1, tombamento);

      ResultSet rs = null;
      try {
         rs = pstmt.executeQuery();

         if (rs.next()) {
            maquina = new Maquina();

            maquina.setId_maquina(rs.getInt("id_maquina"));
            maquina.setCode_pessoa(rs.getInt("id_pessoa"));
            maquina.setCode_setor(rs.getInt("id_setor"));
            maquina.setTombamento(rs.getInt("tombamento"));
            maquina.setSistemaOp(rs.getString("sistema_op"));
         }
      } catch (Exception e) {
         return null;
      } finally {
         pstmt.close();
         if (rs != null) {
            rs.close();
         }
      }
      return maquina;
   }

   /**
    *
    * @param tombamento
    * @return
    * @throws Exception
    */
   public Integer retrieveIdMaquina(Integer tombamento) throws Exception {

      Integer idMaquina = null;
      java.sql.Connection conn = Connection.getInstance();
      PreparedStatement pstmt = conn.prepareStatement(MaquinaQuery.retrieveIdMaquina());
      pstmt.setObject(1, tombamento, Types.INTEGER);

      ResultSet rs = null;
      try {
         rs = pstmt.executeQuery();
         if (rs.next()) {
            idMaquina = rs.getInt("id_maquina");
         }
      } catch (Exception e) {
         throw new Exception(e);
      } finally {
         pstmt.close();
         if (rs != null) {
            rs.close();
         }
      }
      return idMaquina;
   }

   /* realiza upload de um arquivo acerca do host adicionado */
   public boolean insertHostDetails(Maquina maquina, String path) throws Exception {

      /* gerar PDF */
      try {
         ReportHardwareMaquina report = new ReportHardwareMaquina();
         report.setRealpath(path);
         report.setOutputFilename(String.valueOf(maquina.getTombamento()).concat(".pdf"));

         return report.generatePdf(maquina);
         /*java.sql.Connection conn = Connection.getInstance();

         String sql = "INSERT INTO madmac.host_details (hostnumber, caminho) ";
         sql += "VALUES (?, ?) ";
         PreparedStatement pstmt = conn.prepareStatement(sql);
         pstmt.setInt(1, maquina.getTombamento());
         pstmt.setString(2, report.getRealpath().concat(report.getOutputFilename()));
         int result = 0;

         result = pstmt.executeUpdate();

         if (result > 0) {
         works = true;
         }
         pstmt.close();*/
      } catch (Exception e) {
         e.printStackTrace();
         throw new InsertErrorException("Erro ao cadastrar nova máquina.");
      }
   }

   public boolean updateHostDetails(Maquina maquina, String path) throws GenerateScriptErrorException, Exception {
      // DONE: consertar update host_details.
      //return ReportHardwareMaquina.getInstance().generatePdf(maquina);
      try {
         ReportHardwareMaquina report = new ReportHardwareMaquina();
         report.setRealpath(path);
         report.setOutputFilename(String.valueOf(maquina.getTombamento()).concat(".pdf"));

         return report.generatePdf(maquina);
      } catch (Exception e) {
         throw new Exception(e);
      }
   }

   /**
    *
    * @param tombamento
    * @return
    * @throws SQLException
    * @throws ClassNotFoundException
    * @throws Exception
    * @deprecated 
    */
   public String selectHostDetails(int tombamento) throws SQLException, ClassNotFoundException, Exception {

      String path = new String();

      java.sql.Connection conn = Connection.getInstance();
      String sql = "SELECT caminho FROM madmac.host_details WHERE hostnumber = ?";
      PreparedStatement pstmt = conn.prepareStatement(sql);
      pstmt.setInt(1, tombamento);

      ResultSet rs = null;
      try {
         rs = pstmt.executeQuery();

         if (rs.next()) {
            path = rs.getString("caminho");
         }
      } catch (Exception e) {
         throw new Exception(e);
      } finally {
         pstmt.close();
         if (rs != null) {
            rs.close();
         }
      }
      return path;
   }

   public Maquina findByTombamento(int tombamento) throws Exception {

      Maquina maquina = null;
      PreparedStatement pstmt = null;
      ResultSet rs = null;
      try {
         java.sql.Connection conn = Connection.getInstance();
         pstmt = conn.prepareStatement(MaquinaQuery.findByTombamento());
         pstmt.setInt(1, tombamento);
         rs = pstmt.executeQuery();

         if (rs.next()) {
            maquina = new Maquina();
            maquina.setIdMaquina(rs.getInt("id_maquina"));
            maquina.getSetor().setIdSetor(rs.getInt("id_setor"));
            maquina.setTombamento(rs.getInt("tombamento"));
            maquina.getPessoa().setIdPessoa(rs.getInt("id_pessoa"));
            maquina.getPessoa().setNome(rs.getString("responsavel"));
            maquina.setSistemaOp(rs.getString("sistema_op"));
            maquina.getSetor().setNome(rs.getString("setor"));
            maquina.setNomeDominio(rs.getString("nome_dominio"));
         }
      } catch (Exception e) {
         throw new Exception(e);
      } finally {
         if (pstmt != null) {
            pstmt.close();
         }
         if (rs != null) {
            rs.close();
         }
      }
      return maquina;
   }

   public List<Maquina> findAllByIdSetor(Integer idSetor) throws Exception {

      List<Maquina> list = new ArrayList<Maquina>();
      PreparedStatement pstmt = null;
      ResultSet rs = null;
      try {
         java.sql.Connection conn = Connection.getInstance();
         pstmt = conn.prepareStatement(MaquinaQuery.findAllByIdSetor());
         pstmt.setObject(1, idSetor, Types.INTEGER);
         rs = pstmt.executeQuery();

         while (rs.next()) {
            Maquina maquina = new Maquina();
            maquina.setIdMaquina(rs.getInt("id_maquina"));
            maquina.getSetor().setIdSetor(rs.getInt("id_setor"));
            maquina.setTombamento(rs.getInt("tombamento"));
            maquina.getPessoa().setIdPessoa(rs.getInt("id_pessoa"));
            maquina.getPessoa().setNome(rs.getString("responsavel"));
            maquina.setSistemaOp(rs.getString("sistema_op"));
            maquina.getSetor().setNome(rs.getString("setor"));
            maquina.setNomeDominio(rs.getString("nome_dominio"));
            //maquina.setPath(rs.getString("caminho"));

            list.add(maquina);
         }
      } catch (Exception e) {
         throw new Exception(e);
      } finally {
         if (pstmt != null) {
            pstmt.close();
         }
         if (rs != null) {
            rs.close();
         }
      }
      return list;
   }
}
